package com.nf.dao;

import com.nf.entity.Notice;
import com.nf.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class NoticeDaoImpl implements NoticeDao{

    public List<Notice> queryAll() {
        List<Notice> noticeList = new ArrayList();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from notice order by public_date desc";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                Notice n = new Notice();
                n.setId( rs.getString("id")  );
                n.setTitle(  rs.getString("title")  );
                n.setAuthor(  rs.getString("author")  );
                n.setContent( rs.getString("content")  );
                n.setNotice_status( rs.getInt("notice_status") );
                n.setPublic_date( rs.getTimestamp("public_date") );
                noticeList.add(n);
            }

        } catch (Exception e) {
            System.out.println("查询数据库时获取异常:"+e.getMessage());
        }

        DBUtil.close(rs);
        DBUtil.close(pst);
        //关闭连接
        DBUtil.close(conn);
        return noticeList;
    }

    public Notice queryById(String id) {
        Notice n = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from notice where id=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            rs = pst.executeQuery();
            if(rs.next()){
                n = new Notice();
                n.setId( rs.getString("id")  );
                n.setTitle(  rs.getString("title")  );
                n.setAuthor(  rs.getString("author")  );
                n.setContent( rs.getString("content")  );
                n.setNotice_status( rs.getInt("notice_status") );
                n.setPublic_date( rs.getTimestamp("public_date") );
            }

        } catch (Exception e) {
            System.out.println("查询数据库时获取异常:"+e.getMessage());
        }

        DBUtil.close(rs);
        DBUtil.close(pst);
        //关闭连接
        DBUtil.close(conn);
        return n;
    }

    public int updateNotice(Notice notice) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "update notice set title=?,author=?,content=?,notice_status=?,public_date=? where id=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, notice.getTitle());
            pst.setString(2, notice.getAuthor());
            pst.setString(3, notice.getContent());
            pst.setInt(4, notice.getNotice_status());
            pst.setTimestamp(5, new Timestamp( notice.getPublic_date().getTime() ));
            pst.setString(6, notice.getId());
            pst.execute();
            DBUtil.close(pst);
            //关闭连接
            DBUtil.close(conn);
            return 1;

        } catch (Exception e) {
            System.out.println("查询数据库时获取异常:"+e.getMessage());
            DBUtil.close(pst);
            //关闭连接
            DBUtil.close(conn);
            return 0;
        }
    }

    public boolean deleteById(String id) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from notice where id=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            pst.execute();
            DBUtil.close(pst);
            //关闭连接
            DBUtil.close(conn);
            return true;

        } catch (Exception e) {
            System.out.println("查询数据库时获取异常:"+e.getMessage());
            //DBUtil.close(pst);
            //关闭连接
            //DBUtil.close(conn);
            return false;

        }
    }

    public boolean deleteByIds(String[] id) {
        //把数组，变成('aa','bbb','tt')的字符串
        String sql_tmp = "(";
        for (String bb:id){
            sql_tmp = sql_tmp + "'";
            sql_tmp = sql_tmp + bb;
            sql_tmp = sql_tmp + "',";
        }
        sql_tmp = sql_tmp + ")";
        sql_tmp = sql_tmp.replace( ",)",")");



        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from notice where id in "+sql_tmp;
            pst = conn.prepareStatement(sql);
            //pst.setString(1, id);
            pst.execute();
            DBUtil.close(pst);
            //关闭连接
            DBUtil.close(conn);
            return true;

        } catch (Exception e) {
            System.out.println("查询数据库时获取异常:"+e.getMessage());
            //DBUtil.close(pst);
            //关闭连接
            //DBUtil.close(conn);
            return false;

        }
    }

}
